一、 第十二堂相關情境練習

(一) 題目

修改這一堂的範例，計算「每個人」、「每個分工項目」統計排班的次數，並加上排名

(二) 參考答案

SELECT  CleanSchedule_CountResult.FamilyName,
        CleanSchedule_CountResult.ItemName,
        CleanSchedule_CountResult.Count_Number,
        RANK() OVER (ORDER BY CleanSchedule_CountResult.Count_Number DESC) AS CountNumber
FROM ( 
       SELECT DISTINCT
              Family.FamilyName,
              CleanItemList.ItemName,
              COUNT(CleanSchedule.CleanItem) OVER (PARTITION BY Family.FamilyName, CleanItemList.ItemName) AS Count_Number
       FROM family
       INNER JOIN CleanItemList
       ON 1 = 1
       LEFT JOIN CleanSchedule
       ON 	Family.FamilyID = CleanSchedule.FamilyId AND
            CleanItemList.CleanItem = CleanSchedule.CleanItem
     ) AS CleanSchedule_CountResult

(三) 實作結果

(四) 思路講解

1. 產生每個人、每個工作項目的清單

將每個人跟每個打掃項目，透過JOIN ON 1 = 1 的關聯方式產生

SELECT *
FROM Family
INNER JOIN CleanItemList
ON 1 = 1

2. 使用Count函數計算每個人的分工次數

在Count函數後方，透過OVER子句的PARTITION BY，先將資料依照「成員ID」、「負責項目」進行分類，計算每個人在每個分工的排班次數分配

SELECT DISTINCT
       Family.FamilyName,
       CleanItemList.ItemName,
       COUNT(CleanSchedule.CleanItem) OVER (PARTITION BY Family.FamilyId, CleanItemList.ItemName) AS Count_Number
FROM family
INNER JOIN CleanItemList
ON 1 = 1
LEFT JOIN CleanSchedule
ON 	Family.FamilyID = CleanSchedule.FamilyId AND
    CleanItemList.CleanItem = CleanSchedule.CleanItem

3.將子查詢統計出來的排班次數，透過Rank 函數進行排名的處理
在Rank 函數後方，透過OVER子句的ORDER BY，將資料依照排班次數多到排班次數少的進行排序後，給予1、2、3等等排名

SELECT  CleanSchedule_CountResult.FamilyName,
        CleanSchedule_CountResult.ItemName,
        CleanSchedule_CountResult.Count_Number,
        RANK() OVER (ORDER BY CleanSchedule_CountResult.Count_Number DESC) AS CountNumber
FROM ( 
       SELECT DISTINCT
              Family.FamilyName,
              CleanItemList.ItemName,
              COUNT(CleanSchedule.CleanItem) OVER (PARTITION BY Family.FamilyId, CleanItemList.ItemName) AS Count_Number
       FROM family
       INNER JOIN CleanItemList
       ON 1 = 1
       LEFT JOIN CleanSchedule
       ON   Family.FamilyID = CleanSchedule.FamilyId AND
				  CleanItemList.CleanItem = CleanSchedule.CleanItem
     ) AS CleanSchedule_CountResult

(五) 補充：使用DENSE_RANK()讓排名不要遞延

藉由上方的結果可以發現
因為有三個相同的排班次數4排在前面，所以3個會並列第1名
排班次數第二高的3的名次會遞延到「第4名」開始

如果希望名次不要遞延，可以使用DENSE_RANK()函數

二、第十三堂目標：合併不同資料來源但結構相同的查詢

(一) 使用資料表：供應商(Suppliers) 與 客戶(Customers)

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_cast

(二) 需求

找出所有國家位於「德國(Germany)」的供應商與客戶

(三) 實作結果

SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
WHERE Country = 'Germany'
UNION
SELECT 'Supplier' As Type, ContactName, City, Country
FROM Suppliers
WHERE Country = 'Germany'

三、認識聯集查詢：UNION

(一) 常用情境

以範例的「供應商」和「客戶」為例
這兩張資料表的欄位除了「CustomerID」、「SupplierID」、「CustomerName」和「Supplier」不一樣之外
都有「ContactName」、「Address」、「City」、「PostalCode」、「Country」這五個欄位

像這樣子的情境，來自不同資料來源，但結構相同的查詢
可以使用UNION 查詢

(二) 語法

1.UNION：資料來源A的查詢結果 合併 資料來源B的查詢結果，過濾掉兩邊重複的結果

SELECT 資料來源A.欄位1, 資料來源A.欄位2, 資料來源A.欄位3 ... 資料來源A.欄位N
FROM 資料來源A
UNION
SELECT 資料來源B.欄位1, 資料來源B.欄位2, 資料來源B.欄位3 ... 資料來源B.欄位N
FROM 資料來源B

2.UNION ALL：資料來源A的查詢結果 合併 資料來源B的查詢結果，不要過濾掉兩邊重複的結果

SELECT 資料來源A.欄位1, 資料來源A.欄位2, 資料來源A.欄位3 ... 資料來源A.欄位N
FROM 資料來源A
UNION　ALL
SELECT 資料來源B.欄位1, 資料來源B.欄位2, 資料來源B.欄位3 ... 資料來源B.欄位N
FROM 資料來源B

(三) 聯集運算的觀念

1.聯集查詢的兩邊，欄位數量「必須」一致

錯誤範例：

SELECT 'Customer' As Type, ContactName
FROM Customers
WHERE Country = 'Germany'
UNION
SELECT 'Supplier' As Type, ContactName, City, Country
FROM Suppliers
WHERE Country = 'Germany'

錯誤原因：
兩邊查詢欄位數量不一致

Customers的SELECT查詢有「Type」跟「ContactName」2個欄位
Suppliers的SELECT查詢有「Type」、「ContactName」、「City」「 Country」4個欄位

2.聯集查詢的兩邊，欄位型別「建議」一致(DBMS會自動轉換型態)

SELECT BirthDate
FROM Employees
UNION
SELECT SupplierName
FROM Suppliers

在網址或自己的資料庫系統測試後
會發現BirthDate是DateTime，SupplierName是Varchar
卻可以UNION，是因為DBMS幫我們把兩者全部轉換成Varchar做聯集

3.Order BY 只能放在最後一個聯集查詢的後方

正確範例：

SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
WHERE Country = 'Germany'
UNION
SELECT 'Supplier' As Type, ContactName, City, Country
FROM Suppliers
WHERE Country = 'Germany'
ORDER BY ContactName

錯誤範例：

SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
WHERE Country = 'Germany'
ORDER BY ContactName
UNION
SELECT 'Supplier' As Type, ContactName, City, Country
FROM Suppliers
WHERE Country = 'Germany'
ORDER BY ContactName

錯誤原因：
Order By 出現在UNION 聯集查詢的兩邊。

4. UNION ALL：不會過濾掉重複的資料

例如：幫我查詢國家位於「德國」的客戶與供應商的「城市」和「家鄉」

由於位於德國的客戶和應商，都有City = Berlin 和 Country = Germany

如果希望聯集完後不要有重複的資料，在UNION後方不要加上ALL。

四、可自行定義的SELECT欄位

SELECT 區塊除了顯示來自所有查詢來源的資料
還可以自行給予一個字串，定義一個額外的欄位

以這堂的課堂的範例為例：
Customer的聯集查詢，自行定義一個Type欄位，內容是Customer
Suppliers的聯集查詢，自行定義一個Type欄位，內容是Supplier

五、相關情境練習

使用資料：w3resource　Salesman 與 Orders　

https://www.w3resource.com/sql-exercises/union/sql-union-exercise-4.php

題目：每一天訂單最高與最低的業務與訂單數量

請根據「業務」與「訂單」兩張資料表，統計每一天業績最高與業績最低的業務，與業務訂單量
需列出的欄位

• 訂單日期
• 業務名稱
• 資料類別：當天最高/最低 (HIGHEST,LOWEST)
• 對應業績數量

作答方式

備註

1.這題是原本題目的變化題，答案與原題目不同
2.在此網站，UNION後方的查詢請使用()包起來，例如：

SELECT XXX
FROM A
UNION (
SELECT XXX
FROM B
)